﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Data;

public partial class IO_ExcelToDB : System.Web.UI.Page
{
    //  the path where deposit the xls files.
    string filePath = "E:\\Files\\works\\MyCodeShop\\MyWebSite\\IO\\Doc\\";       //  localhost
    //  the path where to upload the files.
    string uploadPath = "E:\\Files\\works\\MyCodeShop\\MyWebSite\\IO\\UploadFolder\\";       //  localhost
    string uploadWebPath = "/IO/UploadFolder/";

    string fileNewNameXls = "upload.xls";
    string fileNewNameCsv = "upload.csv";
    string pathAndFileName = string.Empty;
    string uploadPathAndFileName = string.Empty;

    protected void Page_Load(object sender, EventArgs e)
    {
        gvUpdata_Bind();
    }

    protected void gvUpdata_Bind()
    {
        DataTable dt = DAL.DBTestDAL.GetList();
        gvUpdata.DataSource = dt;
        gvUpdata.DataBind();
    }

    protected void btnUpXls_Click(object sender, EventArgs e)
    {
        string msg = string.Empty;
        string fileName = fupXls.FileName;
        //  get the file path
        pathAndFileName = filePath + fileName;
        //  get the upload path
        uploadPathAndFileName = uploadPath + fileNewNameXls;

        //  delete the old upload.xls file in the upload folder
        if (File.Exists(uploadPathAndFileName))
        {
            try
            {
                File.Delete(uploadPathAndFileName);
            }
            catch (IOException ex)
            { }
        }

        //  upload new file
        if (File.Exists(pathAndFileName))
        {
            try
            {
                fupXls.SaveAs(uploadPathAndFileName);

                //  import data from the upload file to the database table
                if (processXlsUpload())
                {
                    msg = "file was saved to: " + uploadPathAndFileName + ", now process the file....";
                    msg += "<br>" + "file processed successfully!";
                }
                else
                {
                    msg = "file did not process.";
                }
                lblMessageXls.Text = msg;
                lblMessageXls.Visible = true;
            }
            catch (IOException ex)
            {
                //
            }
        }
    }

    protected void btnUpCsv_Click(object sender, EventArgs e)
    {
        
    }

    /// <summary>
    /// Import data from the upload xls file to the database table
    /// </summary>
    /// <returns></returns>
    private bool processXlsUpload()
    {
        bool rtn = false;
        string msg = string.Empty;

        string excelConnString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", uploadPathAndFileName);

        if (File.Exists(uploadPathAndFileName))
        {
            if (uploadPathAndFileName.IndexOf("xlsx") > 0)
            {
                msg = "files were found to be xlsx files, targets are renamed.";
                excelConnString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", uploadPathAndFileName);
            }
        }
        //Response.Write("<br>connstr: <br/>" + excelConnString);
        //Response.End();

        try
        {
            OleDbConnection oconn = new OleDbConnection(excelConnString);
            //OledbConnection and connectionstring to connect to the Excel Sheet

            //After connecting to the Excel sheet here we are selecting the data 
            //using select statement from the Excel sheet
            OleDbCommand ocmd = new OleDbCommand("select * from [employee list$]", oconn);
            oconn.Open();  //Here [Sheet1$] is the name of the sheet 
            //in the Excel file where the data is present
            OleDbDataReader odr = ocmd.ExecuteReader();
            string Id = "";
            string Name= "";
            string Phone = "";
            string Email = "";
            string Address = "";

            int RecordsModified = 0;

            int linesRead = 0;
            while (odr.Read())
            {
                linesRead++;
                Id = valid(odr, 0);
                Name = valid(odr, 1);
                Phone = valid(odr, 2);
                Email = valid(odr, 3);
                Address = valid(odr, 4);

                //Here using this method we are inserting the data into the database
                RecordsModified += InsertDataIntoSql(Name, Phone, Email, Address);
            }

            if (!odr.IsClosed) { odr.Close(); odr.Dispose(); }
            ocmd.Dispose();
            oconn.Close();  oconn.Dispose();

            if (RecordsModified < linesRead)
            {
                lblMessageXls.Text += string.Format("<br /> Warning: {0} records were inserted of {1} total records in the file.", RecordsModified, linesRead);
                lblMessageXls.ForeColor = System.Drawing.Color.Red;
                lblMessageXls.Visible = true;
            }
            else
            {
                gvUpdata_Bind();
                lblMessageXls.Text += string.Format("Data Inserted Sucessfully. Processed {0} of {1} files.", RecordsModified, linesRead);
                lblMessageXls.Visible = true;
            }
        }
        catch (OleDbException ex)
        {
            lblMessageXls.Text += ex.Message;
            lblMessageXls.ForeColor = System.Drawing.Color.Red;
            lblMessageXls.Visible = true;
            rtn = false;
        }
        catch (DataException ex)
        {
            lblMessageXls.Text += ex.Message;
            lblMessageXls.ForeColor = System.Drawing.Color.Red;
            lblMessageXls.Visible = true;
            rtn = false;
        }
        catch (Exception ex)
        {
            //throw ex;
            if (ex.InnerException != null)
            {
                int cntEx = 0;
                while (ex.InnerException != null)
                {
                    lblMessageXls.Text += ex.Message + "<br /><br />" + ex.InnerException.Message;
                    ex = ex.InnerException;
                    cntEx++;
                    if (cntEx > 100)
                        break;
                }
            }
            else
            {
                lblMessageXls.Text += ex.Message;
            }
            lblMessageXls.ForeColor = System.Drawing.Color.Red;
            lblMessageXls.Visible = true;
            rtn = false;
        }
        finally
        {
            lblMessageXls.ForeColor = System.Drawing.Color.Green;
            lblMessageXls.Visible = true;
            rtn = true;
        }

        return rtn;
    }

    /// <summary>
    //This valid method is mainly used to check where the null values are 
    //contained in the Excel Sheet and replacing them with zero
    /// </summary>
    /// <param name="myreader"></param>
    /// <param name="stval"></param>
    /// <returns></returns>
    protected string valid(OleDbDataReader myreader, int stval)//if any columns are found null then they are replaced by zero
    {
        object val = myreader[stval];
        if (val != DBNull.Value)
        {
            return val.ToString();
        }
        else
        {
            return Convert.ToString(0);
        }
    }

    public int InsertDataIntoSql(string Name, string Phone, string Email, string Address)
    {
        //inserting data into the Sql Server
        string SQL = @"insert into dbo.e_Employees (name, phone, email, address, DateEntered) 
                                                                            values (@name, @phone, @email, @address, getdate())";

        SqlParameter[] paras = new SqlParameter[] {
            new SqlParameter("@name", Name),
            new SqlParameter("@phone", Phone),
            new SqlParameter("@email", Email),
            new SqlParameter("@address", Address),
        };

        string connStr = DAL.Conn.getConnString(DAL.Conn.DataBaseName.DBtest);
        return SqlHelper.ExecuteNonQuery(connStr, SQL, paras);
    }

}